package com.zxl.working.mapper.backend;

import com.zxl.working.entity.backend.*;
import com.zxl.working.entity.dto.SellerBillDto;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Qualifier;

import java.util.List;

@Mapper
@Qualifier("backEndSqlSessionTemplate")
public interface InvoiceMapper {

    @Select("select * from seller_bill_tax_type")
    @Results({
            @Result(property = "taxId",column = "tax_id"),
            @Result(property = "taxName",column = "tax_name"),
            @Result(property = "taxDesc",column = "tax_desc"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time")
    })
    List<SellerBillTaxType> qryTaxType();

    @Select("select * from seller_consignee where seller_id = #{sellerId}")
    @Results({
            @Result(property = "recivePersonId",column = "recive_person_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "recivePersonName",column = "recive_person_name"),
            @Result(property = "mobile",column = "mobile"),
            @Result(property = "address",column = "address")
    })
    List<SellerConsignee> qryConsignee(@Param("sellerId") String sellerId);

    @Select("select count(1) from seller_consignee where seller_id = #{sellerId}")
    Integer qryConsigneeCnt(@Param("sellerId") String sellerId);

    @Select("select * from service_info where service_id = #{serviceId}")
    @Results({
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "serviceCom",column = "service_com"),
            @Result(property = "serviceCode",column = "service_code"),
            @Result(property = "serviceTax",column = "service_tax"),
            @Result(property = "serviceOrg",column = "service_org"),
            @Result(property = "serviceLegal",column = "service_legal"),
            @Result(property = "serviceTaxCode",column = "service_tax_code"),
            @Result(property = "serviceAddress",column = "service_address"),
            @Result(property = "servicePhone",column = "service_phone"),
            @Result(property = "serviceBank",column = "service_bank"),
            @Result(property = "serviceAccount",column = "service_account"),
            @Result(property = "serviceState",column = "service_state"),
            @Result(property = "serviceBankName",column = "service_bank_name"),
            @Result(property = "loginId",column = "login_id"),
            @Result(property = "loginName",column = "login_name")
    })
    ServiceInfo qryServiceInfoById(@Param("serviceId") String serviceId);

    @Insert("insert into seller_bill(bill_code,recive_person_name,bill_type_id,express_code,status,taxable_total," +
            "order_id,order_source,seller_company_name,bill_content,seller_id,service_id,upload_id) values(#{billCode},#{recivePersonName},#{billTypeId},#{expressCode},#{status}" +
            ",#{taxableTotal},#{orderId},#{orderSource},#{sellerCompanyName},#{billContent},#{sellerId},#{serviceId},#{uploadId})")
    void insSellerBill(SellerBill sellerBill);

    @Select("<script>" +
            "select a.*,b.seller_name,c.*,c.recive_person_name as rName,d.service_name from seller_bill a,seller_info b,seller_consignee c,service_info d " +
            "where a.seller_id = b.seller_id and a.recive_person_name = c.recive_person_id and a.service_id = d.service_id and a.seller_id = #{sellerId} " +
            "<when test='status!=-1'>" +
            " and a.status = #{status}" +
            "</when>" +
            "order by a.create_time desc limit #{start},#{pageSize}" +
            "</script>")
    @Results({
            @Result(property = "billCode",column = "bill_code"),
            @Result(property = "recivePersonName",column = "recive_person_name"),
            @Result(property = "billTypeId",column = "bill_type_id"),
            @Result(property = "expressCode",column = "express_code"),
            @Result(property = "status",column = "status"),
            @Result(property = "taxableTotal",column = "taxable_total"),
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "orderSource",column = "order_source"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "billContent",column = "bill_content"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "rPersonName",column = "rName"),
            @Result(property = "mobile",column = "mobile"),
            @Result(property = "address",column = "address"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "remark",column = "remark"),
            @Result(property = "expressComName",column = "express_com_name"),
            @Result(property = "billNumber",column = "bill_number")
    })
    List<SellerBillDto> qryBills(@Param("sellerId") String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize,@Param("status") Integer status);

    @Select("<script>" +
            "select count(1) as cnt from seller_bill a,seller_info b,seller_consignee c,service_info d " +
            "where a.seller_id = b.seller_id and a.recive_person_name = c.recive_person_id and a.service_id = d.service_id and a.seller_id = #{sellerId} " +
            "<when test='status!=-1'>" +
            " and a.status = #{status}" +
            "</when>" +
            "</script>")
    Integer qryBillsCnt(@Param("sellerId") String sellerId,@Param("status") Integer status);

    @Select("select * from seller_consignee where seller_id = #{sellerId} limit #{start},#{pageSize}")
    @Results({
            @Result(property = "recivePersonId",column = "recive_person_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "recivePersonName",column = "recive_person_name"),
            @Result(property = "mobile",column = "mobile"),
            @Result(property = "address",column = "address")
    })
    List<SellerConsignee> qryConsigneeList(@Param("sellerId") String sellerId,@Param("start") Integer page,@Param("pageSize") Integer pageSize);

    @Insert("insert into seller_consignee(recive_person_id,seller_id,recive_person_name,mobile,address) values(#{recivePersonId},#{sellerId},#{recivePersonName},#{mobile},#{address})")
    void insConsignee(SellerConsignee sellerConsignee);

    @Update("update seller_consignee set recive_person_name=#{recivePersonName},mobile=#{mobile},address=#{address} where recive_person_id = #{recivePersonId}")
    void updConsignee(SellerConsignee sellerConsignee);

    @Delete("delete from seller_consignee where recive_person_id=#{recivePersonId}")
    void delConsignee(@Param("recivePersonId")String recivePersonId);

    @Select("<script>" +
            "select a.*,b.seller_name,b.seller_company_name,c.service_name from seller_tax a,seller_info b,service_info c " +
            "where a.seller_id = b.seller_id and a.service_id = c.service_id " +
            "<when test='sellerName!=null'>" +
            " and b.seller_name = #{sellerName}" +
            "</when>" +
            "<when test='companyName!=null'>" +
            " and b.seller_company_name = #{companyName}" +
            "</when>" +
            "<when test='serviceName!=null'>" +
            " and c.server_name = #{serviceName}" +
            "</when>" +
            "and a.seller_id = #{sellerId} order by create_time desc limit #{start},#{pageSize}" +
            "</script>")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "taxPic",column = "tax_pic"),
            @Result(property = "taxMonth",column = "tax_month"),
            @Result(property = "taxUrl",column = "tax_url"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
    })
    List<SellerTax> qryTaxs(@Param("sellerId") String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize,
                            @Param("sellerName") String sellerName,@Param("companyName") String companyName,@Param("serviceName") String serviceName);

    @Select("<script>" +
            "select count(1) from seller_tax a,seller_info b,service_info c " +
            "where a.seller_id = b.seller_id and a.service_id = c.service_id " +
            "<when test='sellerName!=null'>" +
            " and b.seller_name = #{sellerName}" +
            "</when>" +
            "<when test='companyName!=null'>" +
            " and b.seller_company_name = #{companyName}" +
            "</when>" +
            "<when test='serviceName!=null'>" +
            " and c.server_name = #{serviceName}" +
            "</when>" +
            "and a.seller_id = #{sellerId} " +
            "</script>")
    Integer qryTaxsCnt(@Param("sellerId") String sellerId,
                       @Param("sellerName") String sellerName,@Param("companyName") String companyName,@Param("serviceName") String serviceName);

    @Select("select a.*,b.seller_id,b.seller_name,c.service_id,c.service_name " +
            "from seller_account_detail a,seller_info b,service_info c " +
            "where a.seller_id = b.seller_id and a.service_id = c.service_id and a.seller_id = #{sellerId} order by a.trade_Id desc limit #{start},#{pageSize}")
    @Results({
            @Result(property = "tradeId",column = "trade_id"),
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "tradeSerial",column = "trade_serial"),
            @Result(property = "orderSource",column = "order_source"),
            @Result(property = "operType",column = "oper_type"),
            @Result(property = "payTime",column = "pay_time"),
            @Result(property = "paySubject",column = "pay_subject"),
            @Result(property = "payType",column = "pay_type"),
            @Result(property = "tradeMoney",column = "trade_money"),
            @Result(property = "tradeComment",column = "trade_comment"),
            @Result(property = "isTotal",column = "is_total"),
            @Result(property = "totalTime",column = "total_time"),
            @Result(property = "cpmTime",column = "cpm_time"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name")
    })
    List<SellerAccountDetails> qryAccountDetails(@Param("sellerId") String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize);

    @Select("select count(1) " +
            "from seller_account_detail a,seller_info b,service_info c " +
            "where a.seller_id = b.seller_id and a.service_id = c.service_id and a.seller_id = #{sellerId}")
    Integer qryAccountDetailsCnt(@Param("sellerId") String sellerId);

    @Select("select sum(invest_amount) from seller_upload_detail where status = 1 and seller_account_id = #{sellerId}")
    Double qryTotalCharge(@Param("sellerId")String sellerId);

    @Select("select sum(taxable_total) from seller_bill a where status != 2 and seller_id = #{sellerId}")
    Double qryTotalBill(@Param("sellerId")String sellerId);

    @Select("SELECT sum(( CASE WHEN b.diffValue <= 0 THEN 0 ELSE 1 END )) " +
            " FROM (SELECT a.provide_amount - IFNULL( d.billTotal, 0 ) AS diffValue " +
            " FROM" +
            " seller_provide_order a" +
            " LEFT JOIN ( SELECT sum( taxable_total ) AS billTotal, seller_id, service_id, order_id FROM seller_bill WHERE `status` IN ( 0, 1 ) GROUP BY seller_id, service_id, order_id ) d ON ( a.seller_id, a.service_id, a.order_id ) = ( d.seller_id, d.service_id, d.order_id ) " +
            " WHERE a.seller_id = #{sellerId} and a.order_type =1) b")
    Integer qryLeftCnt(@Param("sellerId")String sellerId);
}
